package com.jackrain.nea.fc.cp.mapper;

import com.alibaba.fastjson.JSONObject;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.SelectProvider;
import org.apache.ibatis.jdbc.SQL;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 人员档案-服务开发
 * Mapper
 *
 * @author WSL
 * @create 2017/10/30
 */
@Mapper
public interface ViewCempMapper {
    class SelectPro {
        public String selectProvider(Map map) {
            return new SQL() {
                {
                    SELECT("ID,CP_C_DISTRIB_ID,CP_C_STORE_ID,ECODE,ENAME,ENUMNO,CP_C_POST_ID,CP_C_POSTGRADE_ID,ID_CARD,EDUCATION,SEX," +
                            "BIRTHDATE,HIREDATE,OFFICIALDATE,LEAVEDATE,ZIPCODE,MOBIL,TEL,FAX,CONTACTOR,CONTACTOR_TEL,EMAIL,QQ,ADDRESS,REMARK," +
                            "DISCOUNT,SIZE,MODIFIERID,MODIFIEDDATE,ISLEAVE,ISSALER,ISPICKER,ESHORTNO,DISCOUNT,ISOFFICIAL,CP_C_JOB_ID,CP_C_JOBRANK_ID");
                    FROM("CP_C_EMP");
                    Object[] list = (Object[]) map.get("list");
                    StringBuilder sb = new StringBuilder("(");
                    for (Object num : list) {
                        sb.append(num).append(",");
                    }
                    sb.deleteCharAt(sb.length() - 1).append(")");
                    WHERE("ID IN " + sb.toString());
                }
            }.toString();
        }
    }

    /**
     * 根据param查询条件 查询【人员档案】表
     *
     * @param param
     * @return String
     */
    @SelectProvider(type = SelectPro.class, method = "selectProvider")
    List<HashMap> selectProvider(@Param("list") Object[] param);

    class SelectProApi {
        public String selectProvider(Map map) {
            return new SQL() {
                {
                    SELECT("CP_C_DISTRIB_ID,ENAME,SEX,HIREDATE,ID_CARD,CP_C_STORE_ID,MOBIL,ECODE,ENUMNO,REMARK,ADDRESS,CONTACTOR_TEL,CONTACTOR");
                    FROM("CP_C_EMP");
                    Object[] list = (Object[]) map.get("list");
                    StringBuilder sb = new StringBuilder("(");
                    for (Object num : list) {
                        sb.append(num).append(",");
                    }
                    sb.deleteCharAt(sb.length() - 1).append(")");
                    WHERE("ID IN " + sb.toString());
                }
            }.toString();
        }
    }

    /**
     * 根据param查询条件 查询【人员档案】表调用到F4接口
     *
     * @param param
     * @return String
     */
    @SelectProvider(type = SelectProApi.class, method = "selectProvider")
    List<HashMap> selectProviderApi(@Param("list") Object[] param);

    /**
     * 查询【配销中心】ECODE
     *
     * @param id
     * @return String
     */
    @Select("SELECT ECODE FROM CP_C_STOREORG WHERE ID = #{id}")
    String selStoreorgEcode(Long id);

    /**
     * 查询【店仓档案】ECODE
     *
     * @param id
     * @return String
     */
    @Select("SELECT ECODE FROM CP_C_STORE WHERE ID = #{id}")
    String selStoreEcode(Long id);

    /**
     * 查询【岗位】ECODE
     *
     * @param id
     * @return String
     */
    @Select("SELECT ECODE FROM CP_C_POST WHERE ID = #{id}")
    String selPostEcode(Long id);

    /**
     * 查询【岗位级别】ECODE
     *
     * @param id
     * @return String
     */
    @Select("SELECT ECODE FROM CP_C_POSTGRADE WHERE ID = #{id}")
    String selPostgradeEcode(Long id);

    /**
     * 查询最大押金日期
     *
     * @param objid
     * @return Long
     */
    @Select("SELECT MAX(DATEPAY) FROM CP_C_EMP_PAYITEM WHERE CP_C_EMP_ID = #{objid} AND FEETYPE = 1;")
    Long selDeductDate(Long objid);

    /**
     * 查询累计金额
     *
     * @param objid
     * @return Double
     */
    @Select("SELECT SUM(AMTPAY) FROM CP_C_EMP_PAYITEM WHERE CP_C_EMP_ID = #{objid} AND FEETYPE = 1;")
    Double selOrderAmount(Long objid);


    /**
     * 根据传入值查找FC表CP_C_HRORG中HR001_ID=pk_org行的ID
     *
     * @param hrorgId
     * @return JSONObject
     */
    @Select("SELECT COUNT(1) AS COUNT,ID FROM CP_C_HRORG WHERE HR001_ID = #{hrorgId}")
    JSONObject selHrorgId(String hrorgId);

    /**
     * 根据传入值查找FC表CP_C_STORE中HR002_ID=pk_dept行的ID
     *
     * @param storeId
     * @return Long
     */
    @Select("SELECT COUNT(1) AS COUNT,ID FROM CP_C_STORE WHERE HR002_ID = #{storeId}")
    JSONObject selStoreId(String storeId);

    /**
     * 根据店仓ID获取所属部门的上级组织ID
     *
     * @param storeId
     * @return
     */
    @Select("SELECT COUNT(1) AS COUNT,CP_C_HRORG_ID FROM CP_C_STORE WHERE ID = #{storeId}")
    JSONObject selOrgIdByStoreId(Long storeId);

    /**
     * 根据传入值【所属部门】到CP_C_HRORG中HR002_ID=pk_dept行的ID
     *
     * @param hr002
     * @return
     */
    @Select("SELECT COUNT(1) AS COUNT,ID FROM CP_C_HRORG WHERE HR002_ID = #{hr002}")
    JSONObject selOrgIdByDept(String hr002);

    /**
     * 根据传入值查找FC表CP_C_POST中HR004_ID=pk_post行的ID
     *
     * @param postId
     * @return Long
     */
    @Select("SELECT ID FROM CP_C_POST WHERE HR004_ID = #{postId}")
    Long selPostId(String postId);

    /**
     * 判断FC中表【CP_C_EMP】是否存在HR003_ID=Pk_psndoc
     *
     * @param psndoc
     * @return JSONObject
     */
    @Select("SELECT COUNT(1) AS COUNT,ID,CP_C_STORE_ID FROM CP_C_EMP WHERE HR003_ID = #{psndoc}")
    JSONObject checkEmp(String psndoc);

    /**
     * 查询FC中表“职务”【CP_C_JOB】中是否存在HR005_ID=PK_JOB的数据
     *
     * @param pkJob
     * @return
     */
    @Select("SELECT COUNT(1) AS COUNT,ID FROM CP_C_JOB WHERE HR005_ID = #{pkJob}")
    JSONObject getJobId(String pkJob);

    /**
     * 查询FC中表“职等”【CP_C_JOBRANK】中是否存在HR006_ID=PK_JOBRANK的数据
     *
     * @param pkJobRank
     * @return
     */
    @Select("SELECT COUNT(1) AS COUNT,ID FROM CP_C_JOBRANK WHERE HR006_ID = #{pkJobRank}")
    JSONObject getJobRankId(String pkJobRank);

    /**
     * 查询FC中表“职级”【CP_C_POSTGRADE】中是否存在HR007_ID=PK_JOBLEVEL的数据
     *
     * @param pkjobLevel
     * @return
     */
    @Select("SELECT COUNT(1) AS COUNT,ID FROM CP_C_POSTGRADE WHERE HR007_ID = #{pkjobLevel}")
    JSONObject getPostGradeId(String pkjobLevel);

    /**
     * 查询【用户档案】ENAME
     *
     * @param modifierId
     * @return
     */
    @Select("SELECT ENAME FROM USERS WHERE ID = #{modifierId}")
    String selModifyName(Long modifierId);

    /**
     * 查询【职务】ECODE
     *
     * @param jobId
     * @return
     */
    @Select("SELECT ENAME FROM CP_C_JOB WHERE ID = #{jobId}")
    String selcpcJobEcode(Long jobId);

    /**
     * 查询【职等】ECODE
     *
     * @param jobRankId
     * @return
     */
    @Select("SELECT ENAME FROM CP_C_JOBRANK WHERE ID = #{jobRankId}")
    String selcpcJobRankEcode(Long jobRankId);

    @Select("SELECT STORENATURE FROM CP_C_STORE WHERE ID=#{storeId}")
    String selIsSto(Long storeId);

    /**
     * 根据编号查询分拣员工号
     * @param ecode
     * @return
     */
    @Select("SELECT ESHORTNO FROM CP_C_EMP WHERE ECODE=#{ecode}")
    String selEshortno(String ecode);

}
